using System;
using System.Collections.Generic;
using System.Text;
using Emlyn.Common.Data.DBUpdater;
using Emlyn.Common.Data.SqlServer.SqlManagers;

namespace Emlyn.Website.EmlynORegan.DBUpdateRules
{
    public class DUR_0050_AddArticleListTreeStructure : IDBUpdaterRule
    {
        #region IDBUpdaterRule Members

        public int UpgradeFromVersion()
        {
            return 40;
        }

        public int UpgradeToVersion()
        {
            return 50;
        }

        public void PerformUpgrade()
        {
            string ltblEOArticleList_ArticleList =
                "CREATE TABLE [dbo].[tblEOArticleList_ArticleList] ( " +
                "	[ArticleList_ArticleListID] [uniqueidentifier] NOT NULL , " +
                "	[ArticleListID] [uniqueidentifier] NOT NULL , " +
                "	[ChildArticleListID] [uniqueidentifier] NOT NULL  " +
                ") ";
            SqlManager.Execute(ltblEOArticleList_ArticleList);

            string ltblEOArticleList_ArticleListPK =
                "ALTER TABLE [dbo].[tblEOArticleList_ArticleList] WITH NOCHECK ADD  " +
                "	CONSTRAINT [PK_tblEOArticleList_ArticleList] PRIMARY KEY  CLUSTERED  " +
                "	( " +
                "		[ArticleList_ArticleListID] " +
                "	)  ";
            SqlManager.Execute(ltblEOArticleList_ArticleListPK);

            string ldefaultPK = 
                "ALTER TABLE [dbo].[tblEOArticleList_ArticleList] ADD " +
	            "   CONSTRAINT [DF_tblEOArticleList_ArticleList_ArticleList_ArticleListID] " +
                "       DEFAULT (newid()) FOR [ArticleList_ArticleListID] ";
            SqlManager.Execute(ldefaultPK);

            string lforeignKeys =
                "ALTER TABLE [dbo].[tblEOArticleList_ArticleList] ADD  " +
                "	CONSTRAINT [FK_tblEOArticleList_ArticleList_Parent] FOREIGN KEY  " +
                "	( " +
                "		[ArticleListID] " +
                "	) REFERENCES [dbo].[tblEOArticleList] ( " +
                "		[ArticleListID] " +
                "	) ON DELETE CASCADE  ON UPDATE CASCADE , " +
                "	CONSTRAINT [FK_tblEOArticleList_ArticleList_Children] FOREIGN KEY  " +
                "	( " +
                "		[ChildArticleListID] " +
                "	) REFERENCES [dbo].[tblEOArticleList] ( " +
                "		[ArticleListID] " +
                "	)  "; // can't have cascades, SQL Server freaks out
            SqlManager.Execute(lforeignKeys);
        }

        #endregion
    }
}
